/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package personal.dao;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import personal.obj.Category;
import personal.utility.DBUtility;

/**
 *
 * @author PHATNBSE61057
 */
public class CategoryDAO implements Serializable {

    private final DBUtility utility = new DBUtility();

    public CategoryDAO() {
    }

    //<editor-fold defaultstate="collapsed" desc="SEARCHING FUNCTION SECTOR">
    public List<Category> getAllCategory() {
        Connection con = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        Category cate = null;
        List<Category> result = new ArrayList<Category>();
        try {
            con = utility.makeConnection();
            String query = "SELECT * FROM Category";
            pst = con.prepareStatement(query);
            rs = pst.executeQuery();
            while (rs.next()) {
                String categoryID = rs.getString("CategoryID");
                String categoryName = rs.getString("CategoryName");
                String categoryLink = rs.getString("CategoryLink");
                cate = new Category(categoryID, categoryName, categoryLink);
                result.add(cate);
            }
            return result;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (pst != null) {
                    pst.close();
                }
                if (con != null) {
                    con.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return null;
    }

    public List<Category> getCategoryByID(String cateID) {
        Connection con = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        Category cate = null;
        List<Category> result = new ArrayList<Category>();
        try {
            con = utility.makeConnection();
            String query = "SELECT * FROM Category WHERE CategoryID = ?";
            pst = con.prepareStatement(query);
            pst.setString(1, cateID);
            rs = pst.executeQuery();
            while (rs.next()) {
                String categoryID = rs.getString("CategoryID");
                String categoryName = rs.getString("CategoryName");
                String categoryLink = rs.getString("CategoryLink");
                cate = new Category(categoryID, categoryName, categoryLink);
                result.add(cate);
            }
            return result;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (pst != null) {
                    pst.close();
                }
                if (con != null) {
                    con.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return null;
    }

    public List<String> getAllCategoryName() {
        Connection con = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        List<String> result = new ArrayList<String>();
        try {
            con = utility.makeConnection();
            String query = "SELECT CategoryName FROM Category";
            pst = con.prepareStatement(query);
            rs = pst.executeQuery();
            while (rs.next()) {
                String categoryName = rs.getString("CategoryName");
                result.add(categoryName);
            }
            return result;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (pst != null) {
                    pst.close();
                }
                if (con != null) {
                    con.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return null;
    }

    public String getCategoryIDByName(String categoryName) {
        Connection con = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        String categoryID = "";
        try {
            con = utility.makeConnection();
            String query = "SELECT * FROM Category WHERE CategoryName = ?";
            pst = con.prepareStatement(query);
            pst.setString(1, categoryName);
            rs = pst.executeQuery();
            if (rs.next()) {
                categoryID = rs.getString("CategoryID");
            }
            return categoryID;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (pst != null) {
                    pst.close();
                }
                if (con != null) {
                    con.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return null;
    }
    //</editor-fold>

    public boolean updateCategoryInfo(String cateID, String cateName) {
        Connection con = null;
        PreparedStatement pst = null;
        int rowChanged;
        try {
            con = utility.makeConnection();
            String query = "UPDATE Category SET CategoryName = ? WHERE CategoryID = ?";
            pst = con.prepareStatement(query);
            pst.setString(1, cateName);
            pst.setString(2, cateID);
            rowChanged = pst.executeUpdate();
            if (rowChanged > 0) {
                return true;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (pst != null) {
                    pst.close();
                }
                if (con != null) {
                    con.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return false;
    }

    public boolean insertCategory(Category cate) {
        Connection con = null;
        PreparedStatement pst = null;
        int rowChanged;
        try {
            con = utility.makeConnection();
            String query = "INSERT INTO Category (CategoryID, CategoryName, CategoryLink) VALUES (?,?)";
            pst = con.prepareStatement(query);
            pst.setString(1, cate.getCategoryID());
            pst.setString(2, cate.getCategoryName());
            pst.setString(3, cate.getCategoryLink());
            rowChanged = pst.executeUpdate();
            if (rowChanged > 0) {
                return true;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (pst != null) {
                    pst.close();
                }
                if (con != null) {
                    con.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return false;
    }

    public boolean deleteCategory(String cateRemoveID) {
        BookDAO bDAO = new BookDAO();
        boolean remvoveAllBookInCate = bDAO.deleteAllBookInCategory(cateRemoveID);
        if (remvoveAllBookInCate) {
            Connection con = null;
            PreparedStatement pst = null;
            int rowChanged = 0;
            try {
                con = utility.makeConnection();
                String query = "DELETE FROM Category WHERE CategoryID = ?";
                pst = con.prepareStatement(query);
                pst.setString(1, cateRemoveID);
                rowChanged = pst.executeUpdate();
                if (rowChanged > 0) {
                    return true;
                }
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                try {
                    if (pst != null) {
                        pst.close();
                    }
                    if (con != null) {
                        con.close();
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
        return false;
    }
}
